from sqlalchemy import Column, Integer, String, Float, DateTime, Text, ForeignKey, Boolean, DECIMAL, BigInteger, TIMESTAMP
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from datetime import datetime
from pydantic import BaseModel
from typing import List, Optional

Base = declarative_base()

# user
class User(Base):
    __tablename__ = 'user'
    user_id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False, unique=True)
    email = Column(String(100), nullable=False, unique=True)
    password = Column(String(100), nullable=False)
    role = Column(String(20), nullable=False)
    created_time = Column(DateTime, default=datetime.now)
    student_profile = relationship("Student", back_populates="user", uselist=False)
    teacher_profile = relationship("Teacher", back_populates="user", uselist=False)
    learning_records = relationship("LearningRecord", back_populates="user")

# student
class Student(Base):
    __tablename__ = 'student'
    student_id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('user.user_id'), nullable=False)
    name = Column(String(50), nullable=False)
    email = Column(String(100), nullable=False, unique=True)
    password = Column(String(100), nullable=False)
    major = Column(String(50))
    enrollment_year = Column(Integer)
    user = relationship("User", back_populates="student_profile")
    enrollments = relationship("CourseEnrollment", back_populates="student")
    exam_results = relationship("ExamResult", back_populates="student")

# teacher
class Teacher(Base):
    __tablename__ = 'teacher'
    teacher_id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('user.user_id'), nullable=False)
    name = Column(String(50), nullable=False)
    email = Column(String(100), nullable=False, unique=True)
    password = Column(String(100), nullable=False)
    department = Column(String(50))
    user = relationship("User", back_populates="teacher_profile")
    courses = relationship("Course", back_populates="teacher")

# course
class Course(Base):
    __tablename__ = 'course'
    course_id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False)
    description = Column(Text)
    teacher_id = Column(Integer, ForeignKey('teacher.teacher_id'), nullable=False)
    created_time = Column(DateTime, default=datetime.now)
    teacher = relationship("Teacher", back_populates="courses")
    enrollments = relationship("CourseEnrollment", back_populates="course")
    resources = relationship("Resource", back_populates="course")
    assignments = relationship("Assignment", back_populates="course")
    exams = relationship("Exam", back_populates="course")

# course_enrollment
class CourseEnrollment(Base):
    __tablename__ = 'course_enrollment'
    enrollment_id = Column(Integer, primary_key=True, autoincrement=True)
    student_id = Column(Integer, ForeignKey('student.student_id'), nullable=False)
    course_id = Column(Integer, ForeignKey('course.course_id'), nullable=False)
    enrollment_date = Column(DateTime, default=datetime.now)
    progress = Column(Float, default=0.0)
    student = relationship("Student", back_populates="enrollments")
    course = relationship("Course", back_populates="enrollments")

# resource
class Resource(Base):
    __tablename__ = 'resource'
    resource_id = Column(Integer, primary_key=True, autoincrement=True)
    course_id = Column(Integer, ForeignKey('course.course_id'), nullable=False)
    title = Column(String(100), nullable=False)
    description = Column(String(255), nullable=False)
    type = Column(String(20), nullable=False)
    url = Column(String(255))
    version = Column(String(20))
    uploaded_time = Column(DateTime, default=datetime.now)
    course = relationship("Course", back_populates="resources")

# assignment
class Assignment(Base):
    __tablename__ = 'assignment'
    assignment_id = Column(Integer, primary_key=True, autoincrement=True)
    course_id = Column(Integer, ForeignKey('course.course_id'), nullable=False)
    title = Column(String(100), nullable=False)
    description = Column(Text)
    due_date = Column(DateTime, nullable=False)
    created_time = Column(DateTime, default=datetime.now)
    course = relationship("Course", back_populates="assignments")
    submissions = relationship("AssignmentSubmission", back_populates="assignment")

# assignment_submission
class AssignmentSubmission(Base):
    __tablename__ = 'assignment_submission'
    submission_id = Column(Integer, primary_key=True, autoincrement=True)
    assignment_id = Column(Integer, ForeignKey('assignment.assignment_id'), nullable=False)
    student_id = Column(Integer, ForeignKey('student.student_id'), nullable=False)
    content = Column(Text)
    submitted_time = Column(DateTime, default=datetime.now)
    score = Column(Float)
    feedback = Column(Text)
    assignment = relationship("Assignment", back_populates="submissions")
    student = relationship("Student")

# assignment_question_bank
class AssignmentQuestion(Base):
    __tablename__ = 'assignment_question_bank'
    asqb_id = Column(Integer, primary_key=True, autoincrement=True, comment='题目ID')
    content = Column(Text, nullable=False, comment='题目内容')
    type = Column(String(50), nullable=False, comment='题目类型（如：单选题、多选题、填空题、简答题等）')
    options = Column(Text, comment='选项（JSON格式，适用于选择题）')
    answer = Column(Text, comment='标准答案')
    analysis = Column(Text, comment='答案解析')
    difficulty = Column(String(25), default='1', comment='难度等级（1-5）')
    course_id = Column(Integer, comment='课程ID')
    assignment_id = Column(Integer, comment='作业ID')
    student_option = Column(Text, comment='学生作答选项（JSON或文本格式）')
    is_correct = Column(Boolean, comment='用户答案是否正确')
    score_earned = Column(Float, comment='该题得分')

# exam
class Exam(Base):
    __tablename__ = 'exam'
    exam_id = Column(Integer, primary_key=True, autoincrement=True)
    course_id = Column(Integer, ForeignKey('course.course_id'), nullable=False)
    title = Column(String(100), nullable=False)
    description = Column(Text)
    exam_date = Column(DateTime, nullable=False)
    created_time = Column(DateTime, default=datetime.now)
    course = relationship("Course", back_populates="exams")
    results = relationship("ExamResult", back_populates="exam")

# exam_result
class ExamResult(Base):
    __tablename__ = 'exam_result'
    result_id = Column(Integer, primary_key=True, autoincrement=True)
    exam_id = Column(Integer, ForeignKey('exam.exam_id'), nullable=False)
    student_id = Column(Integer, ForeignKey('student.student_id'), nullable=False)
    score = Column(Float)
    feedback = Column(Text)
    exam = relationship("Exam", back_populates="results")
    student = relationship("Student", back_populates="exam_results")

# feedback
class Feedback(Base):
    __tablename__ = 'feedback'
    feedback_id = Column(Integer, primary_key=True, autoincrement=True)
    student_id = Column(Integer, ForeignKey('student.student_id'), nullable=False)
    course_id = Column(Integer, ForeignKey('course.course_id'), nullable=False)
    content = Column(Text, nullable=False)
    created_time = Column(DateTime, default=datetime.now)

# learning_record
class LearningRecord(Base):
    __tablename__ = 'learning_record'
    record_id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('user.user_id'), nullable=False)
    resource_id = Column(Integer, ForeignKey('resource.resource_id'), nullable=False)
    study_time = Column(Float)
    completion_rate = Column(Float)
    interaction_count = Column(Integer, default=0)
    created_time = Column(DateTime, default=datetime.now)
    user = relationship("User", back_populates="learning_records")

# user_answered_questions
class UserAnsweredQuestion(Base):
    __tablename__ = 'user_answered_questions'
    usq_id = Column(BigInteger, primary_key=True, autoincrement=True, comment='记录ID，自增主键')
    exam_id = Column(Integer, comment='考试ID，关联考试表 (如果题目来自考试)')
    course_id = Column(Integer, nullable=False, comment='课程ID，关联课程表')
    question_content = Column(Text, nullable=False, comment='题目内容 (冗余，方便查询)')
    options = Column(Text, comment='选项内容 (JSON格式，冗余，方便查询)')
    correct_answer = Column(Text, comment='正确答案 (冗余，方便比对)')
    user_answer = Column(Text, comment='用户答案')
    question_difficulty = Column(String(20), comment='题目难度 (冗余)')
    is_correct = Column(Boolean, comment='用户答案是否正确')
    answer_time = Column(TIMESTAMP, nullable=False, comment='答题时间')
    score_earned = Column(DECIMAL(5,2), comment='该题得分')
    notes = Column(Text, comment='笔记/备注')

# Pydantic 模型（用于API）
class UserCreate(BaseModel):
    username: str
    email: str
    password: str
    role: str

class UserResponse(BaseModel):
    user_id: int
    username: str
    email: str
    role: str
    created_time: datetime

class CourseCreate(BaseModel):
    title: str
    description: Optional[str] = None
    category: Optional[str] = None
    difficulty_level: Optional[str] = None

class CourseResponse(BaseModel):
    course_id: int
    title: str
    description: Optional[str]
    category: Optional[str]
    difficulty_level: Optional[str]
    created_time: datetime

class LearningAnalysisRequest(BaseModel):
    student_id: int
    course_id: Optional[int] = None

class LearningAnalysisResponse(BaseModel):
    student_id: int
    course_id: Optional[int]
    progress: float
    strength_areas: List[str]
    weakness_areas: List[str]
    study_time: float
    recommendations: List[str]

class RecommendationRequest(BaseModel):
    student_id: int
    limit: Optional[int] = 10

class RecommendationResponse(BaseModel):
    student_id: int
    recommended_courses: List[dict]
    recommended_resources: List[dict]
    reason: str

class ChatRequest(BaseModel):
    question: str
    role: Optional[str] = "student"
    context: Optional[str] = None

class ChatResponse(BaseModel):
    answer: str
    question: str
    related_resources: List[str]
    confidence: float